
Create   function fn_get_specific_process_processstep_id(@process_step_id bigint,@process_step_type varchar(50),@process_step_name varchar(20))

returns  @fn_result Table(ProcessStep_ID bigint,PS_Type varchar(50),Process_Type_Name varchar(20),Product_Name varchar(50),Product_ID int)

as
begin
declare @all_process_steps int
declare @temp1_process_step_id varchar(50)
declare @part_number varchar(50)
declare @product_id int

declare @temp_process_step_id int
declare @temp_ps_type varchar(20)
declare @temp_ps_type_name varchar(20)
declare @temp_product_name varchar(50)
declare @temp_product_id int



declare @result Table(ProcessStep_ID bigint,PS_Type varchar(50),Process_Type_Name varchar(20),
			Product_Name varchar(50),Product_ID int)	


if(@process_step_name != 'Finished Goods')
	begin
		DECLARE allProcessStepsCursor  CURSOR 
		FOR select product.ProcessStep_ID from product, Process_Step where Process_Step.PS_Type_Name = 'Finished Goods' and Process_Step.ProcessStep_ID = Product.ProcessStep_ID and product.Is_Approved = 0
			open allProcessStepsCursor
				fetch next from allProcessStepsCursor into @all_process_steps
					while @@fetch_status = 0
					begin

						DECLARE  specificProcessStepTypeCursor CURSOR 
							FOR select *  from fn_get_process_flow_by_processstep_id (@all_process_steps) where Process_Type_Name=@process_step_name
								open specificProcessStepTypeCursor
									fetch next from specificProcessStepTypeCursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name, @temp_product_name,@temp_product_id
					while @@fetch_status = 0
					begin
						  insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name, Product_Name,Product_ID) values(@temp_process_step_id, @temp_ps_type, @temp_ps_type_name, @temp_product_name,@temp_product_id)

								fetch next from specificProcessStepTypeCursor into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name, @temp_product_name,@temp_product_id
					end

						 		close  specificProcessStepTypeCursor
								deallocate specificProcessStepTypeCursor

					fetch next from  allProcessStepsCursor  into @all_process_steps
					end
			close allProcessStepsCursor
			deallocate allProcessStepsCursor
	
		insert @fn_result select distinct processStep_ID as processStep_ID,PS_Type,Process_Type_Name,Product_Name,Product_ID  from @result
		return
	 end
     else
	begin
			DECLARE allProcessStepsCursorFinishedGoods  CURSOR 
			FOR select product.ProcessStep_ID,process_step.PS_Type,process_step.PS_Type_Name,product.Part_Number,product.Product_id from product, Process_Step where Process_Step.PS_Type_Name = 'Finished Goods' and Process_Step.ProcessStep_ID = Product.ProcessStep_ID and product.Is_Approved = 0
				open allProcessStepsCursorFinishedGoods
					fetch next from allProcessStepsCursorFinishedGoods into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name, @temp_product_name,@temp_product_id
						while @@fetch_status = 0
						begin
							insert into @result(ProcessStep_ID, PS_Type, Process_Type_Name, Product_Name,Product_ID) values(@temp_process_step_id, @temp_ps_type, @temp_ps_type_name, @temp_product_name,@temp_product_id)
		
								fetch next from allProcessStepsCursorFinishedGoods into @temp_process_step_id, @temp_ps_type, @temp_ps_type_name, @temp_product_name,@temp_product_id
						end
				close allProcessStepsCursorFinishedGoods
				deallocate allProcessStepsCursorFinishedGoods

		insert @fn_result select distinct processStep_ID as processStep_ID,PS_Type,Process_Type_Name,Product_Name,Product_ID  from @result
		return
	end

	return 
end

